package dao;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;

import database.DatabaseManager;

import bean.AuctionItemBean;
import bean.BidBean;

/**
 * Data Access Object with static functions concerning the AuctionItem table
 * within the database
 * 
 * @author Sander Meijer & Stijn Kliemesch
 */
public class AuctionItemDAO {

	/**
	 * Gets a single auction item bean for the given ID
	 * 
	 * @param auctionID int
	 * @return {@link AuctionItemBean}
	 */
	public static AuctionItemBean getAuctionByID(int auctionID) {

		AuctionItemBean retBean = null;
		PreparedStatement psGetAuctionByID;
		ResultSet rsGetAuctionByID;
		String getAuctionByIDQuery = 
			"SELECT AUCTIONITEM.*, USERS.USERNAME " +
			"FROM AUCTIONITEM, USERS " +
			"WHERE AUCTIONITEM.IDAUCTIONITEM = ? " +
			"AND AUCTIONITEM.OWNER = USERS.IDUSER";

		try {
			psGetAuctionByID = DatabaseManager.getInstance().getConnection()
					.prepareStatement(getAuctionByIDQuery);
			
			psGetAuctionByID.setInt(1, auctionID);
			rsGetAuctionByID = psGetAuctionByID.executeQuery();

			while (rsGetAuctionByID.next()) {
				if (retBean != null)
					throw new IllegalStateException();
				// This is thrown when more than one result is found, meaning
				// something went terribly wrong.

				String owner = rsGetAuctionByID.getString("Username");
				retBean = createAuctionItemBeanAllBids(rsGetAuctionByID);
				retBean.setOwner(owner);
			}

			psGetAuctionByID.close();
			rsGetAuctionByID.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return retBean;
	}

	/**
	 * Gets all the auctions of a certain owner with the given searchTerm
	 * @param ownerID int
	 * @param searchTerm String
	 * @return ArrayList of {@link AuctionItemBean}s
	 */
	public static ArrayList<AuctionItemBean> getAuctionsByOwnerIDAndSearch(
			int ownerID, String searchTerm) {
		
		ArrayList<AuctionItemBean> retList = new ArrayList<AuctionItemBean>();

		PreparedStatement psGetAuctionsByOwnerIDAndSearch;
		ResultSet rsGetAuctionsByOwnerIDAndSearch;
		String getAucionsByOwnerIDAndSearchString = 
			"SELECT * FROM AuctionItem " +
			"WHERE UPPER(Title) LIKE ? " +
			"AND Owner=?";

		try {

			psGetAuctionsByOwnerIDAndSearch = 
				DatabaseManager.getInstance().getConnection()
					.prepareStatement(getAucionsByOwnerIDAndSearchString);
			psGetAuctionsByOwnerIDAndSearch
					.setString(1,"%" + searchTerm.toUpperCase() + "%");
			psGetAuctionsByOwnerIDAndSearch.setInt(2, ownerID);

			rsGetAuctionsByOwnerIDAndSearch = psGetAuctionsByOwnerIDAndSearch
					.executeQuery();

			while (rsGetAuctionsByOwnerIDAndSearch.next()) {
				retList.add(createAuctionItemBean(rsGetAuctionsByOwnerIDAndSearch));
			}

			psGetAuctionsByOwnerIDAndSearch.close();
			rsGetAuctionsByOwnerIDAndSearch.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return retList;
	}

	/**
	 * Gets the auctions that contains the given search term within the title
	 * 
	 * @param searchTerm String to search the title with
	 * @return ArrayList of {@link AuctionItemBean}s
	 */
	public static ArrayList<AuctionItemBean> getSearchedAuctions(
			String searchTerm) {
		
		ArrayList<AuctionItemBean> retList = new ArrayList<AuctionItemBean>();

		PreparedStatement psGetSearchedAuctions;
		ResultSet rsGetSearchedAuctions;
		String getSearchedAuctionsQuery = 
			"SELECT * FROM AuctionItem " +
			"WHERE UPPER(Title) LIKE ?";

		try {
			psGetSearchedAuctions = 
				DatabaseManager.getInstance().getConnection()
					.prepareStatement(getSearchedAuctionsQuery);
			psGetSearchedAuctions
					.setString(1, "%" + searchTerm.toUpperCase()+ "%");
			rsGetSearchedAuctions = psGetSearchedAuctions.executeQuery();

			while (rsGetSearchedAuctions.next()) {
				retList.add(createAuctionItemBean(rsGetSearchedAuctions));
			}

			psGetSearchedAuctions.close();
			rsGetSearchedAuctions.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return retList;
	}

	/**
	 * Retuns the auctions that finish within seven days
	 * 
	 * @return arrayList of {@link AuctionItemBean}s
	 */
	public static ArrayList<AuctionItemBean> getSevenDayAuctions() {
		
		ArrayList<AuctionItemBean> retList = new ArrayList<AuctionItemBean>();

		PreparedStatement psGetSevenDayAuctions;
		ResultSet rsGetSevenDayAuctions;
		String getSevenDayAuctionsQuery = 
			"SELECT * FROM AuctionItem " +
			"WHERE EndTime < ? ORDER BY EndTime ASC";

		long Long_endTime = Calendar.getInstance().getTimeInMillis() + 604800001;
		Date date = new Date(Long_endTime);

		try {
			psGetSevenDayAuctions = DatabaseManager.getInstance()
					.getConnection().prepareStatement(getSevenDayAuctionsQuery);
			psGetSevenDayAuctions.setDate(1, date);
			rsGetSevenDayAuctions = psGetSevenDayAuctions.executeQuery();

			while (rsGetSevenDayAuctions.next()) {
				retList.add(createAuctionItemBean(rsGetSevenDayAuctions));
			}

			psGetSevenDayAuctions.close();
			rsGetSevenDayAuctions.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return retList;
	}

	/**
	 * The most recent Auction from the owner
	 * 
	 * @param userID int
	 * @return {@link AuctionItemBean}
	 */
	public static AuctionItemBean getNewestAuctionFromOwner(int userID) {
		
		AuctionItemBean retBean = null;

		PreparedStatement psGetNewestAuctionFromOwner;
		ResultSet rsGetNewestAuctionFromOwner;
		String getNewestAuctionFromOwnerQuery = 
			"SELECT * FROM AuctionItem " +
			"WHERE Owner=? ORDER BY EndTime DESC";

		try {
			psGetNewestAuctionFromOwner = 
				DatabaseManager.getInstance().getConnection()
					.prepareStatement(getNewestAuctionFromOwnerQuery);
			psGetNewestAuctionFromOwner.setInt(1, userID);
			psGetNewestAuctionFromOwner.setMaxRows(1);

			rsGetNewestAuctionFromOwner = 
				psGetNewestAuctionFromOwner.executeQuery();

			while (rsGetNewestAuctionFromOwner.next()) {
				retBean = createAuctionItemBean(rsGetNewestAuctionFromOwner);
			}
			
			psGetNewestAuctionFromOwner.close();
			rsGetNewestAuctionFromOwner.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return retBean;
	}

	/**
	 * Gets you a list with auctions you made a bid on, filtered with the given
	 * search term.
	 * 
	 * @param userID int
	 * @param searchParameter String
	 * @return ArrayList<{@link AuctionItemBean}>
	 */
	public static ArrayList<AuctionItemBean> getAuctionsWithBidsFromUserIDdAndSearch(
			int userID, String searchParameter) {
		
		ArrayList<AuctionItemBean> retList = new ArrayList<AuctionItemBean>();

		PreparedStatement psGetAuctionsUserIDBidAndSearch;
		ResultSet rsGetAuctionsUserIDBidAndSearch;
		String getAuctionsUserIDBidAndSearchQuery = 
			"SELECT * FROM AuctionItem " +
			"WHERE IDAuctionItem IN " +
				"(SELECT AuctionID " +
				 "FROM AuctionBids " +
				 "WHERE UserID = ?) " + 
			"AND UPPER(Title) like ? ";

		try {
			psGetAuctionsUserIDBidAndSearch = 
				DatabaseManager.getInstance().getConnection()
					.prepareStatement(getAuctionsUserIDBidAndSearchQuery);
			psGetAuctionsUserIDBidAndSearch.setInt(1, userID);
			psGetAuctionsUserIDBidAndSearch
					.setString(2, "%" + searchParameter.toUpperCase() + "%");

			rsGetAuctionsUserIDBidAndSearch = 
				psGetAuctionsUserIDBidAndSearch.executeQuery();

			while (rsGetAuctionsUserIDBidAndSearch.next()) {
				retList.add(createAuctionItemBeanAllBids(rsGetAuctionsUserIDBidAndSearch));
			}
			
			psGetAuctionsUserIDBidAndSearch.close();
			rsGetAuctionsUserIDBidAndSearch.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return retList;
	}

	/**
	 * Insersts a new auction to the database with the given parameters
	 * 
	 * @param title string
	 * @param price int
	 * @param description string
	 * @param ownerID int
	 * @return boolean success
	 */
	public static void insertNewAuction(String title, int price,
			String description, int ownerID) {

		PreparedStatement psInsertNewAuction;
		String insertNewAuctionQuery = 
			"INSERT INTO AuctionItem (Title, Description, StartDate, StartPrice, EndTime, Owner) "
				+ "VALUES (?, ?, ?, ?, ?, ? )";

		Date startDate = new Date(Calendar.getInstance().getTimeInMillis());
		long fourweeks = 2419200000l;
		Date endTime = new Date(startDate.getTime() + fourweeks);

		try {
			psInsertNewAuction = 
				DatabaseManager.getInstance().getConnection()
					.prepareStatement(insertNewAuctionQuery);

			psInsertNewAuction.setString(1, title);
			psInsertNewAuction.setString(2, description);
			psInsertNewAuction.setDate(3, startDate);
			psInsertNewAuction.setInt(4, price);
			psInsertNewAuction.setDate(5, endTime);
			psInsertNewAuction.setInt(6, ownerID);

			psInsertNewAuction.execute();
			psInsertNewAuction.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Updates the auction of the given ID, need to hand over all parameters.
	 * 
	 * @param title string
	 * @param price int
	 * @param description string
	 * @param auctionID int
	 */
	public static void updateAuctionByID(String title, String description,
			int auctionID) {

		PreparedStatement psUpdateAuctionByID;
		String updateAuctionByIDQuery = 
			"UPDATE AuctionItem SET Title=?, Description=? " +
			"WHERE IDAuctionItem=?";

		try {
			psUpdateAuctionByID = 
				DatabaseManager.getInstance().getConnection()
					.prepareStatement(updateAuctionByIDQuery);

			psUpdateAuctionByID.setString(1, title);
			psUpdateAuctionByID.setString(2, description);
			psUpdateAuctionByID.setInt(3, auctionID);

			psUpdateAuctionByID.execute();
			psUpdateAuctionByID.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Deletes the auction with the given ID
	 * 
	 * @param auctionID int
	 */
	public static void deleteAuctionByAuctionID(int auctionID) {
		
		BidDAO.deleteBidByAuctionID(auctionID);
		PreparedStatement psDeleteAuctionByAuctionID;
		String deleteAuctionsByAuctionID = 
			"DELETE FROM AuctionItem WHERE IDAuctionItem=? ";
		try {
			psDeleteAuctionByAuctionID = 
				DatabaseManager.getInstance().getConnection()
					.prepareStatement(deleteAuctionsByAuctionID);
			psDeleteAuctionByAuctionID.setInt(1, auctionID);
			
			psDeleteAuctionByAuctionID.execute();
			psDeleteAuctionByAuctionID.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Creates an auction item bean from the given result set
	 * 
	 * @param result
	 * @return
	 * @throws SQLException
	 */
	private static AuctionItemBean createAuctionItemBean(ResultSet result)
			throws SQLException {
		
		AuctionItemBean bean;

		int auctionItemID = result.getInt("IDAuctionItem");
		String title = result.getString("Title");
		String description = result.getString("Description");
		int startPrice = result.getInt("StartPrice");
		Date startDate = result.getDate("StartDate");
		long endTime = result.getTimestamp("endTime").getTime();
		int userID = result.getInt("Owner");
		BidBean topBid = BidDAO.getTopBidForAuction(auctionItemID);

		bean = new AuctionItemBean(auctionItemID, title, description,
				startPrice, startDate, endTime, userID,
				new ArrayList<BidBean>(), topBid);

		return bean;
	}

	/**
	 * Creates an auction item bean with all the bids in it
	 * 
	 * @param result
	 * @return
	 * @throws SQLException
	 */
	private static AuctionItemBean createAuctionItemBeanAllBids(ResultSet result)
			throws SQLException {
		
		AuctionItemBean bean = createAuctionItemBean(result);

		ArrayList<BidBean> bids = new ArrayList<BidBean>();
		bids.addAll(BidDAO.getBidsForAuctionID(bean.getAuctionItemID()));

		bean.setBids(bids);

		return bean;
	}
}
